<?php
// Create new PHPExcel object
$objPHPExcel = new PHPExcel();

//Set properties
$objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(0)->setWidth(5);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(1)->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(2)->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimensionByColumn(3)->setWidth(20);

$objPHPExcel->getActiveSheet()->getRowDimension(3)->setRowHeight(23);
$objPHPExcel->getActiveSheet()->getStyle('A3:D3')->getFill()->getStartColor()->setARGB('dadada');
$objPHPExcel->getActiveSheet()->getStyle('A3:D3')->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle('A3:D3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);

// Add some data
$objPHPExcel->getActiveSheet()->getStyle('A1:D1')->getFont()->setName('Times New Roman');
$objPHPExcel->getActiveSheet()->getStyle('A1:D1')->getFont()->setSize(12);
$objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(25);
$objPHPExcel->setActiveSheetIndex(0)->mergeCells('A1:D1')->setCellValue("A1","Report Global Excel Periode ".date("d-m-Y",strtotime($tgl_awal))." s/d ".date("d-m-Y",strtotime($tgl_akhir)));
$objPHPExcel->getDefaultStyle()->getFont()->setName('Calibri');

$objPHPExcel->setActiveSheetIndex(0)
            ->mergeCells('A3:A4')->setCellValue('A3','No')
			->mergeCells('B3:C4')->setCellValue('B3','Karakteristik')
            ->mergeCells('D3:D4')->setCellValue('D3','Prosentase');

/********************Hasil*******************/
$a = 4;
$objPHPExcel->setActiveSheetIndex(0)
		    ->mergeCells('A5:A8')->setCellValue('A5', "1")
		    ->mergeCells('B5:B8')->setCellValue('B5', "Hasil");
			$t_hasil = 0;
			$sangat_puas = 0;
			$puas = 0;
			$kurang_puas = 0;
			$tidak_puas = 0;

		    if($laporan_hasil == NULL || $laporan_hasil == "")
			{
				echo "<script>
					alert('Data tidak ditemukan !');
					location.href='r_global';
				</script>";
			}
			else
			{
				foreach ($laporan_hasil as $row) {
					$t_hasil++;
					if( $row["HASIL"] >= 1 && $row["HASIL"] <= 1.75 ){
						$tidak_puas++;
					} elseif( $row["HASIL"] >= 1.76 && $row["HASIL"] <= 2.5 ) {
						$kurang_puas++;
					} elseif( $row["HASIL"] >= 2.51 && $row["HASIL"] <= 3.25 ) {
						$puas++;
					} else {
						$sangat_puas++;
					}
				}
				
				$objPHPExcel->setActiveSheetIndex(0)
							->setCellValue('C5', "Sangat Puas")
							->setCellValue('D5', round(($sangat_puas/$t_hasil*100),2) . "%")
							->setCellValue('C6', "Puas")
							->setCellValue('D6', round(($puas/$t_hasil*100),2) . "%")
							->setCellValue('C7', "Kurang Puas")
							->setCellValue('D7', round(($kurang_puas/$t_hasil*100),2) . "%")
							->setCellValue('C8', "Tidak Puas")
							->setCellValue('D8', round(($tidak_puas/$t_hasil*100),2) . "%");
			}
/********************End*******************/


/********************Usia*******************/
$t_usia = 0;
$awal	= 9;
$a		= 8;
foreach ($laporan_usia as $row) {
	$t_usia	= $t_usia + $row["USIA"];
}
foreach ($laporan_usia as $row) {
	$a++;
	$objPHPExcel->setActiveSheetIndex(0)
				->setCellValue('C'.$a, $row["range_usia"])
				->setCellValue('D'.$a, round(($row["USIA"]/$t_usia*100),2) . "%");
}
$objPHPExcel->setActiveSheetIndex(0)
		    ->mergeCells('A'.$awal.':A'.$a)->setCellValue('A9', "2")
		    ->mergeCells('B'.$awal.':B'.$a)->setCellValue('B9', "Kelompok Umur");
/********************End*******************/

/********************Pendidikan*******************/		
$t_pendidikan = 0;
$awal = $a;			
foreach ($laporan_pendidikan as $row) {
	$t_pendidikan	= $t_pendidikan + $row["PENDIDIKAN"];
}
foreach ($laporan_pendidikan as $row) {
	$a++;
	$objPHPExcel->setActiveSheetIndex(0)
		->setCellValue('C'.$a, $row["nm_pendidikan"])
		->setCellValue('D'.$a, round(($row["PENDIDIKAN"]/$t_pendidikan*100),2) . "%");
}
$objPHPExcel->setActiveSheetIndex(0)
    ->mergeCells('A'.($awal+1).':A'.($a))->setCellValue('A'.($awal+1), "3")
    ->mergeCells('B'.($awal+1).':B'.($a))->setCellValue('B'.($awal+1), "Pendidikan");
/********************End*******************/

/********************Jenis Kelamin*******************/	
$t_pekerjaan = 0;
$awal = $a;
foreach ($laporan_pekerjaan as $row) {
	$t_pekerjaan	= $t_pekerjaan + $row["PEKERJAAN"];
}
foreach ($laporan_pekerjaan as $row) {
	$a++;
	$objPHPExcel->setActiveSheetIndex(0)
		->setCellValue('C'.$a, $row["nm_pekerjaan"])
		->setCellValue('D'.$a, round(($row["PEKERJAAN"]/$t_pekerjaan*100),2) . "%");
}
$objPHPExcel->setActiveSheetIndex(0)
   ->mergeCells('A'.($awal+1).':A'.($a))->setCellValue('A'.($awal+1), "4")
   ->mergeCells('B'.($awal+1).':B'.($a))->setCellValue('B'.($awal+1), "Pekerjaan");
/********************End*******************/

/********************Frekuensi Urus Izin*******************/
$t_urus_izin = 0;
$awal = $a;
foreach ($laporan_izin as $row) {
	$t_urus_izin	= $t_urus_izin + $row["IZIN"];
}
foreach ($laporan_izin as $row) {
	$a++;
	$objPHPExcel->setActiveSheetIndex(0)
	->setCellValue('C'.$a, $row["urus_izin"] . " kali")
	->setCellValue('D'.$a, round(($row["IZIN"]/$t_urus_izin*100),2) . "%");
}
$objPHPExcel->setActiveSheetIndex(0)
    ->mergeCells('A'.($awal+1).':A'.($a))->setCellValue('A'.($awal+1), "5")
    ->mergeCells('B'.($awal+1).':B'.($a))->setCellValue('B'.($awal+1), "Frekuensi Urus Izin");
/********************End*******************/

/********************Pertanyaan*******************/
$t_pertanyaan = 0;
$awal = $a;
foreach ($laporan_pertanyaan as $row) {
	$t_pertanyaan	= $t_pertanyaan + $row["URUTAN"];
}
foreach ($laporan_pertanyaan as $row) {
	$a++;
	$objPHPExcel->setActiveSheetIndex(0)
	->setCellValue('C'.$a, "Pertanyaan ke-". $row["URUTAN"])
	->setCellValue('D'.$a, round($row["HASIL"],2));
}
$objPHPExcel->setActiveSheetIndex(0)
    ->mergeCells('A'.($awal+1).':A'.($a))->setCellValue('A'.($awal+1), "6")
    ->mergeCells('B'.($awal+1).':B'.($a))->setCellValue('B'.($awal+1), "Pertanyaan (max. 4)");
/********************End*******************/
	
// $objPHPExcel->getActiveSheet()->getStyle("A4:D".$a)->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$objPHPExcel->getActiveSheet()->getStyle("A4:D".$a)->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);

// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle('Report_Global_Excel');

// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);

if($status == 1){
	$objDrawing = new PHPExcel_Worksheet_Drawing();
	$objDrawing->setName('Chart Global');
	$objDrawing->setDescription('Foto Chart Global');
	$objDrawing->setPath('screenshot.png');
	$objDrawing->setCoordinates('F2'); 
	$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
}

// Redirect output to a client’s web browser (Excel2007)
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="Report_Global_Excel.xlsx"');
header('Cache-Control: max-age=0');

$objWriter = IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');
exit;
